#!/usr/bin/python3
# -*- coding: utf-8 -*-
import hmac
import hashlib
import urllib
import urllib.parse
import json
import time
import base64
import requests
import pymysql
import traceback

host = 'gz-cdb-mzcky1pf.sql.tencentcdb.com'
port = 62348
username = 'wangzhijing'
password = 'wangzhijing'
dbname = 'cmcater_log'
try:
    db = pymysql.connect(host, username, password, dbname, port)
except Exception:
    traceback.print_exc()
    time.sleep(5)
    db = pymysql.connect(host, username, password, dbname, port)

datalist = ["{'order_id': '20060180100002943', 'packing_charges': '6.00'}",
            "{'order_id': '20060180100004611', 'packing_charges': '20.00'}",
            "{'order_id': '20060280100003812', 'packing_charges': '5.00'}",
            "{'order_id': '20060380100007043', 'packing_charges': '6.00'}",
            "{'order_id': '20060480200000891', 'packing_charges': '7.00'}",
            "{'order_id': '20060480100007686', 'packing_charges': '6.00'}",
            "{'order_id': '20060480100007792', 'packing_charges': '6.00'}",
            "{'order_id': '20060480100007792', 'packing_charges': '8.00'}",
            "{'order_id': '20060580100007931', 'packing_charges': '6.00'}",
            "{'order_id': '20060680100002907', 'packing_charges': '8.00'}",
            "{'order_id': '20060680100005063', 'packing_charges': '6.00'}",
            "{'order_id': '20060680100007147', 'packing_charges': '10.00'}",
            "{'order_id': '20060780100008123', 'packing_charges': '5.00'}",
            "{'order_id': '20060780100008123', 'packing_charges': '8.00'}",
            "{'order_id': '20060880100002776', 'packing_charges': '5.00'}",
            "{'order_id': '20060880100006937', 'packing_charges': '8.00'}",
            "{'order_id': '20061080100008824', 'packing_charges': '7.00'}",
            "{'order_id': '20061180100007457', 'packing_charges': '6.00'}",
            "{'order_id': '20061180100007459', 'packing_charges': '6.00'}",
            "{'order_id': '20061180100008986', 'packing_charges': '6.00'}",
            "{'order_id': '20061180100009289', 'packing_charges': '8.00'}",
            "{'order_id': '20061180100009289', 'packing_charges': '5.00'}",
            "{'order_id': '20061180100009362', 'packing_charges': '10.00'}",
            "{'order_id': '20061180100009362', 'packing_charges': '5.00'}",
            "{'order_id': '20061180100009377', 'packing_charges': '5.00'}",
            "{'order_id': '20061180100009377', 'packing_charges': '10.00'}"]


def query_package_amount():
    order_date = f'order_goods_{time.strftime("%Y%m")}'
    package_amount_sql = "SELECT order_id, packing_charges FROM " + order_date + " WHERE packing_charges >= 5.00"
    cursor = db.cursor(pymysql.cursors.DictCursor)
    try:
        cursor.execute(package_amount_sql)
    except Exception:
        traceback.print_exc()
        db_try = pymysql.connect(host, username, password, dbname, port)
        cursor = db_try.cursor(pymysql.cursors.DictCursor)
    finally:
        time.sleep(1)
        results = cursor.fetchall()
        # results = dataBase(package_amount_sql)
        # print(results)
        # f = open('./datalist.txt', 'w')
        # num = 0
        for result in results:
            content = str(result).replace('Decimal(', '').replace(')', '')
            # print(content)
            if content in datalist:
                continue
                # f.writelines(content + '\n')
            else:
                dingtalk_publish(content)
                datalist.append(content)
                # time.sleep(5)
                # num += 1
                # if num < 20:
                #     dingtalk_publish(content)
                #     time.sleep(1)
                # else:
                #     time.sleep(5)
                #     dingtalk_publish(content)
                # datalist.append(content)
        # f.close()


def signature():
    times = int(round(time.time() * 1000))
    # 调试 secret = 'SEC12808f1d15e1e4722ab587c65ff28edaf00962701d3116cc698f1add5e820208'
    # 测试 secret = 'SEC3a0275898d1f366619e0f953e374a1226259deb788c71f3771fadf38c619df6f'
    # 测试三组 secret = 'SECf14b256db35631e40c2db06dd60256d63f3074d05699eb71b2bdcbad61766dce'
    secret = 'SEC12808f1d15e1e4722ab587c65ff28edaf00962701d3116cc698f1add5e820208'
    secret_enc = str(secret).encode('utf-8')
    string_to_sign = '{}\n{}'.format(times, secret)
    string_to_sign_enc = str(string_to_sign).encode('utf-8')
    hmac_code = hmac.new(secret_enc, string_to_sign_enc, digestmod=hashlib.sha256).digest()
    sign = urllib.parse.quote(base64.b64encode(hmac_code))
    return times, sign


def dingtalk_publish(content):
    times, sign = signature()
    # 调试 https://oapi.dingtalk.com/robot/send?access_token=6d9595d480610e650bd7462de5525249c44ffb0c7f47f66346ed44f8ed89bc20
    # 测试 https://oapi.dingtalk.com/robot/send?access_token=a48ee18f5c005ee2c348d4a76a50223ebaa1fd761929533ae972735b10062f40
    # 测试三组 https://oapi.dingtalk.com/robot/send?access_token=8ae81394957488b16a7ad972c6263c2670a4fd4e2daa636f65dcbbfc43e1f216
    url = 'https://oapi.dingtalk.com/robot/send?access_token=6d9595d480610e650bd7462de5525249c44ffb0c7f47f66346ed44f8ed89bc20' + '&timestamp=' + str(
        times) + '&sign=' + sign
    data = {
        "msgtype": "text",
        "text": {
            "content": content
        },
        "at": {
            "atMobiles": [
                "176xxxx3205",
                "189xxxx8325"
            ],
            "isAtAll": True
        }
    }
    headers = {
        'Content-Type': 'application/json'
    }
    response = requests.post(url, data=json.dumps(data), headers=headers)
    result = response.json()
    print(result)
    print(str(time.strftime("%H:%M:%S")) + "推送成功")


while True:
    query_package_amount()
    time.sleep(10)
    # f = open('./datalist.txt', 'r')
    # file = f.read()
    # f.close()
    # print(datalist)
    # time.sleep(10)
